package com.zzxz.practicaltrainingsys.mapper;

import com.zzxz.practicaltrainingsys.entity.Weeklynews;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface WeeklynewsMapper {

    //查询指定组、项目下，组长已提交的周报
    @Select("SELECT w.* " +
            "FROM weeklynews w " +
            "JOIN team t ON w.project_id = t.project_id " +
            "WHERE t.project_id = #{projectId} " +
            "  AND t.group_id = #{groupId} " +
            "  AND t.leader_id = w.student_id")
    List<Weeklynews> getLeaderWeeklyByGroup(@Param("projectId") Integer projectId,
                                            @Param("groupId") Integer groupId);

    //周报查询（只有组长才能提交）
    @Select("SELECT \n" +
            "  p.project_name,\n" +
            "  s.student_id,\n" +
            "  s.student_name,\n" +
            "  COALESCE(w.weeklynews_content, '') AS weeklynews_content, \n" +
            "  COALESCE(w.weeklynews_time, NULL) AS weeklynews_time, \n" +
            "  t.group_id AS group_number, \n" +
            "  '组长' AS position_type\n" +
            "FROM \n" +
            "  student s\n" +
            "  JOIN project p ON EXISTS (\n" +
            "    SELECT 1 FROM team t \n" +
            "    WHERE t.project_id = p.project_id \n" +
            "    AND s.student_id = t.leader_id  \n" +
            "  )\n" +
            "  LEFT JOIN weeklynews w \n" +
            "    ON s.student_id = w.student_id \n" +
            "    AND p.project_id = w.project_id \n" +
            "  LEFT JOIN team t \n" +
            "    ON p.project_id = t.project_id \n" +
            "    AND s.student_id = t.leader_id\n" +
            "ORDER BY \n" +
            "  p.project_name, \n" +
            "  s.student_name, \n" +
            "  w.weeklynews_time DESC;")
    List<Map<String, Object>> getAllWeeklyNews();

    // 周报查询（只有组长才能提交），获取某个项目的周报
    @Select("SELECT \n" +
            "  p.project_name,\n" +
            "  s.student_id,\n" +
            "  s.student_name,\n" +
            "  COALESCE(w.weeklynews_content, '') AS weeklynews_content, \n" +
            "  COALESCE(w.weeklynews_time, NULL) AS weeklynews_time, \n" +
            "  t.group_id AS group_number, \n" +
            "  '组长' AS position_type\n" +
            "FROM \n" +
            "  student s\n" +
            "  JOIN project p ON EXISTS (\n" +
            "    SELECT 1 FROM team t \n" +
            "    WHERE t.project_id = p.project_id \n" +
            "    AND s.student_id = t.leader_id  \n" +
            "  )\n" +
            "  LEFT JOIN weeklynews w \n" +
            "    ON s.student_id = w.student_id \n" +
            "    AND p.project_id = w.project_id \n" +
            "  LEFT JOIN team t \n" +
            "    ON p.project_id = t.project_id \n" +
            "    AND s.student_id = t.leader_id\n" +
            "WHERE \n" +
            "  p.project_id = #{projectId}\n" +
            "ORDER BY \n" +
            "  p.project_name, \n" +
            "  s.student_name, \n" +
            "  w.weeklynews_time DESC;")
    List<Map<String, Object>> getWeeklyNewsByProjectId(@Param("projectId") Integer projectId);

    //用来联合学生表的学号来查该项目该学生的周报
    @Select("SELECT w.* FROM student s " +
            "LEFT JOIN weeklynews w ON s.student_id = w.student_id " +
            "WHERE s.student_id = #{studentId} " )
    List<Weeklynews> getWeeklynewsByStudentId(
            @Param("studentId") Integer studentId);

    //周报详情
    @Select("SELECT * FROM weeklynews WHERE weeklynews_id = #{weeklynewsId}")
    Weeklynews getWeeklynewsById(Integer weeklynewsId);

    //新增周报
    @Insert("INSERT INTO weeklynews (student_id, weeklynews_content, weeklynews_time, project_id) " +
            "VALUES (#{student_id}, #{weeklynews_content}, sysdate(), #{project_id})")
    int insertWeeklynews(Weeklynews weeklynews);

    //更新周报
    @Update("UPDATE weeklynews " +
            "SET weeklynews_content = #{weeklynews_content}, " +
            "    weeklynews_time = #{weeklynews_time} " +
            "WHERE weeklynews_id = #{weeklynews_id}")
    int updateWeeklynews(Weeklynews weeklynews);

}
